﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySkiPatrolEntity;

namespace MySkiPatrolSQL
{
    public static class PatrolSQL
    {
        public static string GetPatrolList()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from patrol order by PatrolName");
            return sql.ToString();
        }

        public static string GetPatrolBySubDomain(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from patrol where subdomain = '" + data.SubDomain + "'");
            return sql.ToString();
        }

        public static string GetPatrolByID(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from patrol where PatrolID = '" + data.ID + "'");
            return sql.ToString();
        }

        public static string AddPatrol(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into patrol (");
            sql.Append("PatrolID,PatrolName,PatrolTitle,subdomain,dbprefix");
            sql.Append(") values (");
            sql.Append("'" + data.ID + "',");
            sql.Append("'" + data.Name + "',");
            sql.Append("'" + data.Title + "',");
            sql.Append("'" + data.SubDomain + "',");
            sql.Append("'" + data.dbPrefix + "'");
            sql.Append(")");
            return sql.ToString();
        }

        public static string GetAppSettings(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + data.DataTransaction.PatrolPrefix + "_ApplicationSetting ");
            return sql.ToString();
        }

        #region Create new patrol tables
        public static string CreateMemberTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");
            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_Member]( ");
            sql.Append("	[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("	[FirstName] [varchar](25) NOT NULL, ");
            sql.Append("	[LastName] [varchar](50) NOT NULL, ");
            sql.Append("	[Username] [varchar](50) NOT NULL, ");
            sql.Append("	[Password] [varchar](150) NOT NULL, ");
            sql.Append("	[NSPNumber] [varchar](10) NULL, ");
            sql.Append("	[DateDeleted] [datetimeoffset] NULL, ");
            sql.Append(" CONSTRAINT [PK_" + data.dbPrefix + "_Member] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("	[MemberID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("ALTER TABLE dbo.bss_Member ADD CONSTRAINT DF_" + data.dbPrefix + "_Member_MemberID DEFAULT (newid()) FOR MemberID ");
            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateEmailTemplateTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");
            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_EmailTemplate]( ");
            sql.Append("[EmailTemplateID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[EmailTemplateName] [varchar](50) NOT NULL, ");
            sql.Append("[EmailTemplateText] [text] NULL, ");
            sql.Append("[EmailTemplateHTML] [text] NULL ");
            sql.Append(") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ");
            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateEmailLogTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");
            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_EmailLog]( ");
            sql.Append("[LogID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[Status] [varchar](5) NOT NULL, ");
            sql.Append("[SentDateTime] [datetimeoffset] NOT NULL, ");
            sql.Append("[SentFrom] [varchar](100) NOT NULL, ");
            sql.Append("[SentTo] [text] NOT NULL, ");
            sql.Append("[Subject] [varchar](255) NOT NULL, ");
            sql.Append("[Body] [text] NOT NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_EmailLog] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[LogID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ");
            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateEmailAddressTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");
            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_EmailAddress]( ");
            sql.Append("[EmailAddressID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[SmtpAddress] [varchar](150) NOT NULL, ");
            sql.Append("[AddressName] [varchar](10) NOT NULL, ");
            sql.Append("[OptOut] [bit] NOT NULL, ");
            sql.Append("[IsVerified] [bit] NOT NULL, ");
            sql.Append("[IsPrimary] [bit] NOT NULL, ");
            sql.Append("[VerificationCode] [varchar](6) NULL, ");
            sql.Append("[UseHTMLFormat] [bit] NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_emailaddress] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[EmailAddressID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");
            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateApplicationSettingTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_ApplicationSetting]( ");
            sql.Append("[SettingID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[SettingName] [varchar](50) NOT NULL, ");
            sql.Append("[SettingValue] [varchar](2048) NOT NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_ApplicationSetting] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[SettingID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateArticleTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_Article]( ");
            sql.Append("[ArticleID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[Title] [nvarchar](50) NOT NULL, ");
            sql.Append("[Body] [ntext] NOT NULL, ");
            sql.Append("[EffectiveDate] [datetimeoffset] NULL, ");
            sql.Append("[ExpirationDate] [datetimeoffset] NULL, ");
            sql.Append("[PageID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[IsPublic] [bit] NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_Article] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[ArticleID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateGroupRoleTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_GroupRole]( ");
            sql.Append("[GroupRoleID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[GroupID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[RoleID] [uniqueidentifier] NOT NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_GroupRole] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[GroupRoleID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateRoleMemberTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_RoleMember]( ");
            sql.Append("[RoleMemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[RoleID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_RoleMember] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[RoleMemberID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateRoleTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_Role]( ");
            sql.Append("[RoleID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[Name] [varchar](25) NOT NULL, ");
            sql.Append("[Description] [varchar](255) NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_Role] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[RoleID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");


            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateGroupTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_Group]( ");
            sql.Append("[GroupID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[Name] [varchar](25) NOT NULL, ");
            sql.Append("[Description] [varchar](255) NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_group] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[GroupID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");


            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateEventTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_Event]( ");
            sql.Append("[EventID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[EventTitle] [varchar](150) NOT NULL, ");
            sql.Append("[StartDate] [datetimeoffset] NOT NULL, ");
            sql.Append("[EndDate] [datetimeoffset] NULL, ");
            sql.Append("[Location] [varchar](150) NULL, ");
            sql.Append("[Comments] [varchar](500) NULL, ");
            sql.Append("[AllDayEvent] [bit] NOT NULL, ");
            sql.Append("[IsPublic] [bit] NULL, ");
            sql.Append("[VisibleOnRibbon] [bit] NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_event] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[EventID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateServiceDaysTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_ServiceDays]( ");
            sql.Append("[DayID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, ");
            sql.Append("[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[SignIn] [datetimeoffset] NOT NULL, ");
            sql.Append("[SignOut] [datetimeoffset] NULL, ");
            sql.Append("[ServiceTypeID] [smallint] NULL, ");
            sql.Append("[Comment] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_ServiceDays] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[DayID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateDailySignInTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_DailySignIn]( ");
            sql.Append("[SignInID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[SignIn] [datetimeoffset] NOT NULL, ");
            sql.Append("[SignOut] [datetimeoffset] NULL, ");
            sql.Append("CONSTRAINT [PK_" + data.dbPrefix + "_SignIn] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[SignInID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY] ");

            sql.Append("ALTER TABLE dbo." + data.dbPrefix + "_DailySignIn ADD CONSTRAINT DF_" + data.dbPrefix + "_DailySignIn_SignInID DEFAULT (newid()) FOR SignInID ");
            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string CreateMemberIDCardsTable(Patrol data)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SET ANSI_NULLS ON ");
            sql.Append("SET QUOTED_IDENTIFIER ON ");
            sql.Append("SET ANSI_PADDING ON ");

            sql.Append("CREATE TABLE [dbo].[" + data.dbPrefix + "_MemberIDCards]( ");
            sql.Append("[ID] [varchar](50) NOT NULL, ");
            sql.Append("[MemberID] [uniqueidentifier] NOT NULL, ");
            sql.Append("[Expires] [datetimeoffset](7) NULL, ");
            sql.Append("[LastUsed] [datetimeoffset] NULL, ");
            sql.Append("[DateDeleted] [datetimeoffset] NULL, ");
            sql.Append("CONSTRAINT [PK_bss_MemberIDCards] PRIMARY KEY CLUSTERED  ");
            sql.Append("( ");
            sql.Append("[ID] ASC ");
            sql.Append(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ");
            sql.Append(") ON [PRIMARY]");

            sql.Append("SET ANSI_PADDING OFF ");
            return sql.ToString();
        }

        public static string GetWeatherForecasts(string patrolPrefix)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from weatherforecast where patrolprefix = '" + patrolPrefix + "'");
            return sql.ToString();
        }

        public static string UpdateWeatherForecast(WeatherForecast data, string patrolPrefix)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update weatherforecast set ");
            //sql.Append("weatherforecastdata = '" + data.Data.Replace("'", "''") + "', ");
            sql.Append("weatherforecasthtml = '" + data.HTML.Replace("'", "''") + "', ");
            sql.Append("patrolprefix = '" + patrolPrefix + "', ");
            sql.Append("lastupdate = '" + data.LastUpdate + "' ");
            sql.Append("where weatherwatcherid = '" + data.ID + "' ");
            return sql.ToString();
        }


        public static string CreateDefaultGroups(Patrol data)
        {
            //used to create the defaults groups for each site
            StringBuilder sql = new StringBuilder();
            sql.Append("insert into " + data.dbPrefix + "_group ");
            sql.Append("(groupid,name,description) values ");
            sql.Append("('" + System.Guid.NewGuid().ToString() + "','Admin','Site Administration')");
            return sql.ToString();
        }
        #endregion
    }
}
